/***************************************************************************************************
 *
 * Project: GrabChineseLessons
 *
 * Class: HZFindAccessor
 *
 * File name: HZFindAccessor.java
 *
 * Parent class:
 *
 **************************************************************************************************/

package com.eastmountain.chinese.db.flashcards.access;

import java.io.ByteArrayInputStream;
import java.sql.Blob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.eastmountain.chinese.db.exceptions.DBAccessException;
import com.eastmountain.chinese.db.flashcards.connection.DBAccessData;
import com.eastmountain.chinese.db.flashcards.records.ChineseCharacterProperties;
import com.eastmountain.chinese.db.flashcards.records.ChineseElementProperties;
import com.eastmountain.chinese.db.flashcards.records.ChineseWordLessonXRF;
import com.eastmountain.chinese.db.flashcards.records.ChineseWordProperties;
import com.eastmountain.chinese.db.flashcards.records.Lesson;

/*************************************************
 *
 *   CLASS - HZFindAccessor
 *
 ************************************************/

public class HZFindAccessor extends HZAccessor {

    private ResultSet resultSet = null;
    
    private static short latestOrderingValue = 0;

    /**************************************************
     *
     *   LESSONS SQL statements
     *
     **************************************************/

    private PreparedStatement lessonByLessonNumberSelectStmt;
    private static final String lessonByLessonNumberSelectSQL
        = "SELECT * FROM Lessons WHERE lessonNumber = ?";

    private PreparedStatement lessonsByLevelSelectStmt;
    private static final String lessonsByLevelSelectSQL
        = "SELECT * FROM Lessons WHERE level = ?"
                    + " ORDER BY lessonNumber";

    private PreparedStatement minimumLessonNumberSelectStmt;
    private static final String minimumLessonNumberSelectSQL
        = "SELECT MIN( lessonNumber ) FROM Lessons";

    /**************************************************
     *
     *   CHARACTERS SQL statements
     *
     **************************************************/

    private PreparedStatement characterPropertiesByLessonNumberSelectStmt;
    private static final String characterPropertiesByLessonNumberSelectSQL
        = "SELECT * FROM CHARACTERS WHERE lessonNumber = ?";

    private PreparedStatement characterPropertiesByLessonNumberListSelectStmt;
    private static final String characterPropertiesByLessonNumberListSelectSQL
        = "SELECT * FROM CHARACTERS WHERE lessonNumber = ?";
    
    private PreparedStatement characterPropertiesInWordListSelectStmt;
    private static final String characterPropertiesInWordListSelectSQL
        = "SELECT * FROM CHARACTERS WHERE hanzi = ?";

    /**************************************************
     *
     *   CHARACTERLESSONXRF SQL statements
     *
     **************************************************/

    private PreparedStatement characterLessonXRFByHanZiSelectStmt;
    private static final String characterLessonXRFByHanZiSelectSQL
        = "SELECT * FROM CHARACTERLESSONXRF"
        + " WHERE hanZi = ? AND lessonNumber = ? ";

    /**************************************************
     *
     *   WORDS SQL statements
     *
     **************************************************/

    private PreparedStatement wordPropertiesByLessonNumberSelectStmt;
    private static final String wordPropertiesByLessonNumberSelectSQL
         = "SELECT "
         + "word, simplifiedTraditional, "
         + "lessonNumber, soundOrdering, level, "
         + "lessonSection, "
         + "pronunciation, definition, "
         + " fileName, sound "
         + "FROM WORDS WHERE lessonNumber = ?";

    private PreparedStatement nextWordPropertiesInSoundOrderSelectStmt;
    private static final String nextWordPropertiesInSoundOrderSelectSQL
        = "SELECT "
        + "word, simplifiedTraditional, "
        + "lessonNumber, soundOrdering, level, "
        + "lessonSection, "
        + "pronunciation, definition, "
        + "fileName, sound "
        + "FROM WORDS "
        + "WHERE soundOrdering > ? AND level = ?";

    //private PreparedStatement wordPropertiesByLessonNumberListSelectStmt;
    //private static final String wordPropertiesByLessonNumberListSelectSQL
    //    = "SELECT * FROM WORDS WHERE lessonNumber = ?";

    // the following does no work
    
    private PreparedStatement wordsContainingCharacterSelectStmt;
    private static final String wordsContainingCharacterSelectSQL
        = "SELECT "
        + "word, simplifiedTraditional, "
        + "lessonNumber, soundOrdering, level, "
        + "lessonSection, "
        + "pronunciation, definition "
        + "FROM WORDS WHERE word LIKE '?'";

    /**************************************************
     *
     *   WORDLESSONXRF SQL statements
     *
     **************************************************/

    private PreparedStatement wordLessonXRFByWordSelectStmt;
    private static final String wordLessonXRFByWordSelectSQL
        = "SELECT * FROM WORDLESSONXRF"
        + " WHERE word = ? AND lessonNumber = ? "
        + " AND orderInLesson = ?";

    private static String projectName = "chpod-find-data";
    private static Logger logger = Logger.getLogger( projectName );

    public HZFindAccessor( DBAccessData dbURLs ) throws DBAccessException {

        super(  dbURLs );

    }

    /***********************************************************************************************
     *
     * findLessonForLessonNumber
     *
     **********************************************************************************************/

    public Lesson findLessonForLessonNumber( int lessonNumber ) throws DBAccessException {

        Lesson lesson = null;

        try {

            lessonByLessonNumberSelectStmt.setShort( 1, (short)lessonNumber );

            resultSet = lessonByLessonNumberSelectStmt.executeQuery();

            if ( resultSet.first() ) {

                lesson = new Lesson();

                lesson.setLessonNumber( resultSet.getShort( 1 ) );
                lesson.setLevel( resultSet.getString( 2 ) );
                
                if ( resultSet.getString( 3 ).equals( "?" ) ) {
                	lesson.setTitle( "" );
                } else {
                    lesson.setTitle( resultSet.getString( 3 ) );
                }
                
                lesson.setSimplifiedTraditionalLesson( resultSet.getString( 4 ) );
                lesson.setSource( resultSet.getString( 5 ) );
                lesson.setCreationDate( resultSet.getTimestamp( 6 ) );

            }

            resultSet.close();
            resultSet = null;

        } catch ( Exception exception ) {

            tableName = LESSONS;

            reportException( exception, tableName );

        }

        return( lesson );

    }

    /***********************************************************************************************
     *
     * findLessonsForLevel
     *
     **********************************************************************************************/

    public Vector<Lesson> findLessonsForLevel( String level ) throws DBAccessException {

        Vector<Lesson> lessonsByLevel = new Vector<Lesson>();
        Lesson lesson = null;

        try {

            lessonsByLevelSelectStmt.setString( 1, level );

            resultSet = lessonsByLevelSelectStmt.executeQuery();

            while ( resultSet.next() ) {

                lesson = new Lesson();

                lesson.setLessonNumber( resultSet.getShort( 1 ) );
                lesson.setLevel( resultSet.getString( 2 ) );
                
                if ( resultSet.getString( 3 ).equals( "?" ) ) {
                	lesson.setTitle( "" );
                } else {
                    lesson.setTitle( resultSet.getString( 3 ) );
                }
                
                lesson.setSimplifiedTraditionalLesson( resultSet.getString( 4 ) );
                lesson.setSource( resultSet.getString( 5 ) );
                lesson.setCreationDate( resultSet.getTimestamp( 6 ) );

                lessonsByLevel.add( lesson );

            }

            resultSet.close();
            resultSet = null;

        } catch ( Exception exception ) {

            tableName = LESSONS;

            // TODO do within an aspect

            reportException( exception, tableName );

        }

        return (lessonsByLevel);

    }

    /***********************************************************************************************
     *
     * findMinimumLessonNumber
     *
     **********************************************************************************************/

    public int findMinimumLessonNumber() throws DBAccessException {

        int minimumLessonNumber = 0;

        try {

            resultSet = minimumLessonNumberSelectStmt.executeQuery();

            if ( resultSet.first() ) {

                minimumLessonNumber = resultSet.getShort( 1 );

            }

            resultSet.close();
            resultSet = null;

        } catch ( Exception exception ) {

            tableName = LESSONS;

            reportException( exception, tableName );

        }

        return( minimumLessonNumber );

    }

    /***********************************************************************************************
     *
     * findCharacterPropertiesByLessonNumber
     *
     **********************************************************************************************/

    public Vector<ChineseElementProperties> findCharacterPropertiesByLessonNumber(
                    int lessonNumber ) throws DBAccessException {

        System.out.println( "****** findCharacterPropertiesByLessonNumber() invoked");

        Vector<ChineseElementProperties> chineseElementPropertiesForLesson = new Vector<ChineseElementProperties>();
        ChineseCharacterProperties chineseElementProperties = new ChineseCharacterProperties();
        
        Blob soundBlob;
        byte[] soundArray = null;

        try {

            characterPropertiesByLessonNumberSelectStmt.setShort( 1, (short)lessonNumber );

            resultSet = characterPropertiesByLessonNumberSelectStmt.executeQuery();

            while ( resultSet.next() ) {

                chineseElementProperties = new ChineseCharacterProperties();

                chineseElementProperties.setChineseElement( resultSet.getString( 1 ) );
                chineseElementProperties.setSimplifiedTraditional( resultSet.getString( 2 ) );
                chineseElementProperties.setLessonNumber( resultSet.getShort( 3 ) );
                chineseElementProperties.setSoundOrdering( resultSet.getShort( 4 ) );
                chineseElementProperties.setLevel( resultSet.getString( 5 ) );
                chineseElementProperties.setLessonSection( resultSet.getString( 6 ) );
                chineseElementProperties.setPinyin( resultSet.getString( 7 ) );
                chineseElementProperties.setEnglish( resultSet.getString( 8 ) );
                chineseElementProperties.setFileName( resultSet.getString( 9 ) );
                
                soundBlob = resultSet.getBlob( 10 );
                if ( soundBlob != null ) {
                    
                    soundArray = soundBlob.getBytes( (long)1, (int)soundBlob.length() );
                    chineseElementProperties.setSound( new ByteArrayInputStream( soundArray ) );
                       
                } else {
                    
                    chineseElementProperties.setSound( null );
                    
                }

                chineseElementProperties.setKangXiRadicalNumber( resultSet.getShort( 11 ) );
                chineseElementProperties.setSimplifiedRadical( resultSet.getShort( 12 ) );
                chineseElementProperties.setAdditionalStrokes( resultSet.getShort( 13 ) );

                chineseElementPropertiesForLesson.add( chineseElementProperties );

            }

            resultSet.close();
            resultSet = null;

        } catch ( Exception exception ) {

            tableName = CHARACTERS;

            reportException( exception, tableName );

        }

        return( chineseElementPropertiesForLesson );

    }

    /***********************************************************************************************
     *
     * findCharacterPropertiesForWord
     *
     **********************************************************************************************/

    public Vector<ChineseCharacterProperties> findCharacterPropertiesForWord(
                    String word ) throws DBAccessException {

        System.out.println( "****** findCharacterPropertiesForWord() invoked");

        Vector<ChineseCharacterProperties> chineseCharacterPropertiesForWord
            = new Vector<ChineseCharacterProperties>();
        ChineseCharacterProperties chineseCharacterProperties = null;
        
        String character;
        
        Blob soundBlob;
        byte[] soundArray = null;
       	
        for (int i = 0; i < word.length(); i++) {

        	try {

        		character = word.substring( i, i + 1 );
 
        		characterPropertiesInWordListSelectStmt.setString( 1, character );

        		resultSet = characterPropertiesInWordListSelectStmt.executeQuery();

        		while ( resultSet.next() ) {

        			chineseCharacterProperties = new ChineseCharacterProperties();

        			chineseCharacterProperties.setChineseElement( resultSet.getString( 1 ) );
        			chineseCharacterProperties.setSimplifiedTraditional( resultSet.getString( 2 ) );
        			chineseCharacterProperties.setLessonNumber( resultSet.getShort( 3 ) );
                    chineseCharacterProperties.setSoundOrdering( resultSet.getShort( 4 ) );
        			chineseCharacterProperties.setLevel( resultSet.getString( 5 ) );
        			chineseCharacterProperties.setLessonSection( resultSet.getString( 6 ) );
        			chineseCharacterProperties.setPinyin( resultSet.getString( 7 ) );
        			chineseCharacterProperties.setEnglish( resultSet.getString( 8 ) );
                    chineseCharacterProperties.setFileName( resultSet.getString( 9 ) );
/*                    
                    soundBlob = resultSet.getBlob( 10 );
                    soundArray = soundBlob.getBytes( (long)1, (int)soundBlob.length() );
                    chineseCharacterProperties.setSound( new ByteArrayInputStream( soundArray ) );
*/                    
                    soundBlob = resultSet.getBlob( 10 );
                    if ( soundBlob != null ) {
                        
                        soundArray = soundBlob.getBytes( (long)1, (int)soundBlob.length() );
                        chineseCharacterProperties.setSound( new ByteArrayInputStream( soundArray ) );
                           
                    } else {
                        
                        chineseCharacterProperties.setSound( null );
                        
                    }

        			chineseCharacterProperties.setKangXiRadicalNumber( resultSet.getShort( 11 ) );
        			chineseCharacterProperties.setSimplifiedRadical( resultSet.getShort( 12 ) );
        			chineseCharacterProperties.setAdditionalStrokes( resultSet.getShort( 13 ) );

        			chineseCharacterPropertiesForWord.add( chineseCharacterProperties );

        		}

        		resultSet.close();
        		resultSet = null;

        	} catch ( Exception exception ) {

        		tableName = CHARACTERS;

        		reportException( exception, tableName );

        	}

        }

        return( chineseCharacterPropertiesForWord );

    }

    /***********************************************************************************************
     *
     * findCharacterPropertiesByLessonNumberList
     *
     **********************************************************************************************/
/*
    public Vector<ChineseElementProperties> findCharacterPropertiesByLessonNumberList(
                    int[] lessonNumbers ) throws DBAccessException {

        System.out.println( "****** findCharacterPropertiesByLessonNumberList() invoked");

        Vector<ChineseElementProperties> chineseElementPropertiesForLesson = new Vector<ChineseElementProperties>();
        Vector<ChineseElementProperties> chineseElementPropertiesForLessonList = new Vector<ChineseElementProperties>();

        try {

            for ( int element : lessonNumbers ) {

                chineseElementPropertiesForLesson = findCharacterPropertiesByLessonNumber( element );

                System.out.println( "****** chineseElementPropertiesForLesson size: "
                                    + chineseElementPropertiesForLesson.size() );

                boolean  success = chineseElementPropertiesForLessonList.addAll( chineseElementPropertiesForLesson );

            }

        } catch ( java.lang.Exception e ) {

            logger.log( Level.SEVERE, "*** SELECT: Exception:" + e.getClass().getName() + " MSG: "
                            + e.getMessage(), e );

            e.printStackTrace();

            logger.log( Level.SEVERE, "*** End of my Stack Trace" );

            for ( Handler h : logger.getHandlers() ) {
                h.flush();
            }

        }

        System.out.println( "****** chineseElementPropertiesForLessonList size: "
                            + chineseElementPropertiesForLessonList.size() );

        return( chineseElementPropertiesForLessonList );

    }
*/
    /***********************************************************************************************
     *
     * findWordPropertiesByLessonNumber
     *
     **********************************************************************************************/

    public Vector<ChineseElementProperties> findWordPropertiesByLessonNumber(
                    int lessonNumber ) throws DBAccessException {
        
        Blob soundBlob;
        byte[] soundArray = null;

        System.out.println( "****** findWordPropertiesByLessonNumber() invoked");

        Vector<ChineseElementProperties> chineseElementPropertiesForLesson = new Vector<ChineseElementProperties>();
        ChineseElementProperties chineseElementProperties = new ChineseWordProperties();

        try {

            wordPropertiesByLessonNumberSelectStmt.setShort( 1, (short)lessonNumber );

            resultSet = wordPropertiesByLessonNumberSelectStmt.executeQuery();

            while ( resultSet.next() ) {

                chineseElementProperties = new ChineseWordProperties();

                chineseElementProperties.setChineseElement( resultSet.getString( 1 ) );
                chineseElementProperties.setSimplifiedTraditional( resultSet.getString( 2 ) );
                chineseElementProperties.setLessonNumber( resultSet.getShort( 3 ) );
                chineseElementProperties.setSoundOrdering( resultSet.getShort( 4 ) );
                chineseElementProperties.setLevel( resultSet.getString( 5 ) );
                chineseElementProperties.setLessonSection( resultSet.getString( 6 ) );
                chineseElementProperties.setPinyin( resultSet.getString( 7 ) );
                chineseElementProperties.setEnglish( resultSet.getString( 8 ) );
                chineseElementProperties.setFileName( resultSet.getString( 9 ) );
                
                soundBlob = resultSet.getBlob( 10 );
                if ( soundBlob != null ) {
                    
                    soundArray = soundBlob.getBytes( (long)1, (int)soundBlob.length() );
                    chineseElementProperties.setSound( new ByteArrayInputStream( soundArray ) );
                       
                } else {
                    
                    chineseElementProperties.setSound( null );
                    
                }

                chineseElementPropertiesForLesson.add( chineseElementProperties );

            }

            resultSet.close();
            resultSet = null;

        } catch ( Exception exception ) {

            tableName = WORDS;

            reportException( exception, tableName );

        }

        return( chineseElementPropertiesForLesson );

    }

    /*************************************************
     *
     *   getNextWordRecordInSoundOrder
     *
     **************************************************/

    public ChineseElementProperties getNextWordRecordInSoundOrder( String level ) {

        ChineseElementProperties chineseElementProperties = null;
        ResultSet resultSet = null;
        Blob soundBlob;
        byte[] soundArray = null;

        int recordsSelected = 0;

        try {

            nextWordPropertiesInSoundOrderSelectStmt.setShort( 1, latestOrderingValue );
            nextWordPropertiesInSoundOrderSelectStmt.setString( 2, level );

            resultSet = nextWordPropertiesInSoundOrderSelectStmt.executeQuery();

            if ( resultSet.first() ) {

                recordsSelected++;
                
                chineseElementProperties = new ChineseWordProperties();
                
                //recordType = TonesRecord.RecordTypes.getToneRecord_RecordType( resultSet.getString( 5 ) );
                
                // update lateestNextPinYin to pinyin just retrieved

                latestOrderingValue = resultSet.getShort( 4 );
                
                chineseElementProperties.setChineseElement( resultSet.getString( 1 ) );
                chineseElementProperties.setSimplifiedTraditional( resultSet.getString( 2 ) );
                chineseElementProperties.setLessonNumber( resultSet.getShort( 3 ) );
                chineseElementProperties.setSoundOrdering( resultSet.getShort( 4 ) );
                chineseElementProperties.setLevel( resultSet.getString( 5 ) );
                chineseElementProperties.setLessonSection( resultSet.getString( 6 ) );
                chineseElementProperties.setPinyin( resultSet.getString( 7 ) );
                chineseElementProperties.setEnglish( resultSet.getString( 8 ) );
                chineseElementProperties.setFileName( resultSet.getString( 9 ) );
                
                soundBlob = resultSet.getBlob( 10 );
                if ( soundBlob != null ) {
                    
                    soundArray = soundBlob.getBytes( (long)1, (int)soundBlob.length() );
                    chineseElementProperties.setSound( new ByteArrayInputStream( soundArray ) );
                       
                } else {
                    
                    chineseElementProperties.setSound( null );
                    
                }

            }


        } catch ( SQLException ex ) {

            logger.log( Level.SEVERE, "SQLException: " + ex.getMessage()
                    + "\n" + "SQLState: "
                    + ex.getSQLState() + "\n"
                    + "VendorError: " + ex.getErrorCode()
                    + "tonesDBRecord: " + chineseElementProperties );

            ex.printStackTrace();

            logger.log( Level.SEVERE, "Program terminating" );

            System.exit( 12 );

        } catch ( java.lang.Exception e ) {

            logger.log( Level.SEVERE, "*** SELECT: Exception:" + e.getClass().getName() + " MSG: "
                    + e.getMessage() );

            e.printStackTrace();

            logger.log( Level.SEVERE, "Program terminating" );

            System.exit( 12 );

        } finally {

            try {
                resultSet.close();
            } catch ( SQLException e ) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }

        return ( chineseElementProperties );

    }

    /***********************************************************************************************
     *
     * findWordsContainingCharacter
     *
     **********************************************************************************************/

    public Vector<ChineseWordProperties> findWordsContainingCharacter(
                    String character ) throws DBAccessException {

        boolean rowFound;

        System.out.println( "****** findWordsContainingCharacter() invoked");

        Vector<ChineseWordProperties> chineseWordPropertiesForCharacter
            = new Vector<ChineseWordProperties>();
        ChineseWordProperties chineseWordProperties = new ChineseWordProperties();

        try {

            //wordsContainingCharacterSelectStmt.setString( 1, "%" + character.trim() + "%" );

            //resultSet = wordsContainingCharacterSelectStmt.executeQuery();

            Statement stmt
              = flashCardDBConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                           ResultSet.CONCUR_READ_ONLY);

            String query = "SELECT "
                           + "word, simplifiedTraditional, "
                           + "lessonNumber, soundOrdering, level, "
                           + "lessonSection, "
                           + "pronunciation, definition "
                           + " FROM WORDS WHERE word LIKE "
                           + "'%" + character.trim() + "%'";

            logger.log( Level.INFO, "    ABOUT TO EXECUTE: " + query );

            ResultSet resultSet
               = stmt.executeQuery( query );

            rowFound = false;

            while ( resultSet.next() ) {

                rowFound = true;

                chineseWordProperties = new ChineseWordProperties();

                chineseWordProperties.setChineseElement( resultSet.getString( 1 ) );
                chineseWordProperties.setSimplifiedTraditional( resultSet.getString( 2 ) );
                chineseWordProperties.setLessonNumber( resultSet.getShort( 3 ) );
                chineseWordProperties.setSoundOrdering( resultSet.getShort( 4 ) );
                chineseWordProperties.setLevel( resultSet.getString( 5 ) );
                chineseWordProperties.setLessonSection( resultSet.getString( 6 ) );
                chineseWordProperties.setPinyin( resultSet.getString( 7 ) );
                chineseWordProperties.setEnglish( resultSet.getString( 8 ) );

                chineseWordPropertiesForCharacter.add( chineseWordProperties );

            }

            if ( ! rowFound ) {

                logger.log( Level.SEVERE, "No rows retrieved in findWordsContainingCharacter: "
                                          +  this.getClass().getName() );

            }

            resultSet.close();
            resultSet = null;

        } catch ( SQLException ex ) {

 /*           logger.warning( "SQLException: " + ex.getMessage() + "\n" + "SQLState: "
                            + ex.getSQLState() + "\n" + "VendorError: " + ex.getErrorCode() );

            throw new DBAccessException(
                            "Problem acccessing DB. \nError in creating Prepared Statements", ex );

*/
        } catch ( Exception exception ) {

            tableName = WORDS;

            reportException( exception, tableName );

        }

        return( chineseWordPropertiesForCharacter );

    }

    /***********************************************************************************************
     *
     * findWordLessonXRFByWord
     *
     **********************************************************************************************/

    public ChineseWordLessonXRF findWordLessonXRFByWord( ChineseWordLessonXRF wordLessonXRF ) throws DBAccessException {

        System.out.println( "****** findWordLessonXRFByWord() invoked");
        ChineseWordLessonXRF chineseWordLessonXRF = new ChineseWordLessonXRF();

        try {

            wordLessonXRFByWordSelectStmt.setString( 1, wordLessonXRF.getChineseElement() );
            wordLessonXRFByWordSelectStmt.setShort( 2, (short)wordLessonXRF.getLessonNumber() );
            wordLessonXRFByWordSelectStmt.setShort( 3, (short)wordLessonXRF.getOrderInLesson() );

            resultSet = wordLessonXRFByWordSelectStmt.executeQuery();

            //   There should never be more than one row since
            //   all columns are included in the primary key

            if ( resultSet.first() ) {

                chineseWordLessonXRF.setChineseElement( resultSet.getString( 1 ) );
                chineseWordLessonXRF.setSimplifiedTraditional( resultSet.getString( 2 ) );
                chineseWordLessonXRF.setLessonNumber( resultSet.getShort( 3 ) );
                chineseWordLessonXRF.setOrderInLesson( resultSet.getShort( 4 ) );

            }

            resultSet.close();
            resultSet = null;

        } catch ( Exception exception ) {

            tableName = WORDXRF;

            reportException( exception, tableName );

        }

        return( chineseWordLessonXRF );

    }

    /***********************************************************************************************
     *
     * setupPreparedStatements
     *
     **********************************************************************************************/

    @Override
    protected void setupPreparedStatements() throws DBAccessException {

        try {

            logger.info( "***** about to call flashCardDBConnection.prepareStatement()" );

            logger.info( "***** Lesson retrieval statements  *****" );

            /**************************************************
             *   LESSONS SQL statements
             **************************************************/

            logger.info( "===*** Stmt:  " + lessonByLessonNumberSelectSQL );
            lessonByLessonNumberSelectStmt
                = flashCardDBConnection.prepareStatement( lessonByLessonNumberSelectSQL,
                                                ResultSet.TYPE_SCROLL_SENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY );

            logger.info( "===*** Stmt:  " + lessonsByLevelSelectSQL );
            lessonsByLevelSelectStmt = flashCardDBConnection.prepareStatement( lessonsByLevelSelectSQL );

            logger.info( "===*** Stmt:  " + minimumLessonNumberSelectSQL );
            minimumLessonNumberSelectStmt
                = flashCardDBConnection.prepareStatement( minimumLessonNumberSelectSQL,
                        ResultSet.TYPE_SCROLL_SENSITIVE,
                        ResultSet.CONCUR_READ_ONLY );

            /**************************************************
             *   CHARACTERS SQL statements
             **************************************************/

            logger.info( "***** Character retrieval statements  *****" );

            logger.info( "===*** Stmt:  " + characterPropertiesByLessonNumberSelectSQL );
            characterPropertiesByLessonNumberSelectStmt = flashCardDBConnection
                            .prepareStatement( characterPropertiesByLessonNumberSelectSQL );

            logger.info( "===*** Stmt:  " + characterPropertiesByLessonNumberListSelectSQL );
            characterPropertiesByLessonNumberListSelectStmt = flashCardDBConnection
                            .prepareStatement( characterPropertiesByLessonNumberListSelectSQL );

            logger.info( "===*** Stmt:  " + characterPropertiesInWordListSelectSQL );
            characterPropertiesInWordListSelectStmt = flashCardDBConnection
                            .prepareStatement( characterPropertiesInWordListSelectSQL );

            /**************************************************
             *   CHARACTERXRF SQL statements
             **************************************************/

            logger.info( "***** CharacterLessonXRF retrieval statements  *****" );

            logger.info( "===*** Stmt:  " + characterLessonXRFByHanZiSelectSQL );
            characterLessonXRFByHanZiSelectStmt = flashCardDBConnection
                            .prepareStatement( characterLessonXRFByHanZiSelectSQL );

            /**************************************************
             *   WORDS SQL statements
             **************************************************/

            logger.info( "***** Word retrieval statements  *****" );

            logger.info( "===*** Stmt:  " + wordPropertiesByLessonNumberSelectSQL );
            wordPropertiesByLessonNumberSelectStmt = flashCardDBConnection
                            .prepareStatement( wordPropertiesByLessonNumberSelectSQL );

            logger.info( "===*** Stmt:  " + nextWordPropertiesInSoundOrderSelectSQL );
            nextWordPropertiesInSoundOrderSelectStmt = flashCardDBConnection
                            .prepareStatement( nextWordPropertiesInSoundOrderSelectSQL,
                                               ResultSet.TYPE_SCROLL_SENSITIVE,
                                               ResultSet.CONCUR_READ_ONLY );

            //logger.info( "===*** Stmt:  " + wordPropertiesByLessonNumberListSelectSQL );
            //wordPropertiesByLessonNumberListSelectStmt = flashCardDBConnection
            //                .prepareStatement( wordPropertiesByLessonNumberListSelectSQL );

            logger.info( "===*** Stmt:  " + wordsContainingCharacterSelectSQL );
            wordsContainingCharacterSelectStmt = flashCardDBConnection
                            .prepareStatement( wordsContainingCharacterSelectSQL );

            /**************************************************
             *   WORDXRF SQL statements
             **************************************************/

            logger.info( "***** WordLessonXRF retrieval statements  *****" );

            logger.info( "===*** Stmt:  " + wordLessonXRFByWordSelectSQL );
            wordLessonXRFByWordSelectStmt = flashCardDBConnection
                            .prepareStatement( wordLessonXRFByWordSelectSQL );

            logger.info( "*** Statements Prepared" );

        } catch ( SQLException ex ) {

            logger.warning( "SQLException: " + ex.getMessage() + "\n" + "SQLState: "
                            + ex.getSQLState() + "\n" + "VendorError: " + ex.getErrorCode() );

            throw new DBAccessException(
                            "Problem acccessing DB. \nError in creating Prepared Statements", ex );

        } catch ( java.lang.Exception e ) {

            logger.severe( "*** Select: Exception:" + e.getClass().getName() + " MSG: "
                            + e.getMessage() );

        }

    }

    /***********************************************************************************************
     *
     * reportException This function is now handled with aspects
     *
     **********************************************************************************************/

    private void reportException( Exception exception, String tableName ) throws DBAccessException {

        if ( exception instanceof SQLException ) {

            logger.log( Level.SEVERE, "SQLException: " + exception.getMessage()
                            + "\n" + "SQLState: " + ((SQLException)exception).getSQLState()
                            + "\n" + "VendorError: " + ((SQLException)exception).getErrorCode()
                            + "\n" + "For Table: " + tableName
                            + "\n" );

            throw new DBAccessException( "Problem accessing table: " + tableName, exception );

        } else if ( exception instanceof Exception ) {

            logger.log( Level.SEVERE, "*** Unexpected Exception:" + exception.getClass().getName() + " MSG: "
                            + exception.getMessage(), exception
                            + "\n" + "For Table: " + tableName
                            + "\n" );

            exception.printStackTrace();

            logger.log( Level.SEVERE, "*** End of my Stack Trace" );

        }

    }

}